package br.ufrj.neg.reuniao.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import br.ufrj.neg.model.ServiceLocator;
import br.ufrj.neg.reuniao.bean.ParticipanteBean;

public class ParticipanteModel {
	public int cadastraParticipanteReuniao(ParticipanteBean participante) {
		PreparedStatement ps = null;
		Connection conn = ServiceLocator.getConnection();
		try {
			String sql = "insert into participante(part_ds_nome, part_telefone1, part_celular, " +
					"part_ds_cargo, part_tx_observacao) values(?, ?, ?, ?, ?)";

			ps = conn.prepareStatement(sql);
			ps.setString(1, participante.getNome());
			ps.setString(2, participante.getTelefone());
			ps.setString(3, participante.getCelular());
			ps.setString(4, participante.getCargo());
			ps.setString(5, participante.getObservacao());
			ps.executeUpdate();

			sql = "select PART_CD_ID from participante where part_ds_nome = ? and part_telefone1 = ? and "
					+ "part_celular = ? and part_ds_cargo = ? and part_tx_observacao = ?";

			ps = conn.prepareStatement(sql);
			ps.setString(1, participante.getNome());
			ps.setString(2, participante.getTelefone());
			ps.setString(3, participante.getCelular());
			ps.setString(4, participante.getCargo());
			ps.setString(5, participante.getObservacao());
			ResultSet rs = ps.executeQuery();
			rs.next();
			participante.setId(Integer.parseInt(rs.getString("PART_CD_ID")));

			sql = "insert into reuniao_participante(reun_cd_id, "
					+ "part_cd_id) values(?, ?)";

			ps = conn.prepareStatement(sql);
			ps.setInt(1, participante.getIdReuniao());
			ps.setInt(2, participante.getId());
			ps.executeUpdate();

			conn.commit();
			/*
			 * sql = "SELECT max(nego_cd_id) as \"num\" FROM negociacao"; ps =
			 * conn.prepareStatement(sql); ResultSet rs = ps.executeQuery();
			 * conn.commit(); int idNegocio = -1; while (rs.next()){ idNegocio =
			 * rs.getInt("num"); }
			 * 
			 * NegociacaoElementoModel nem = new NegociacaoElementoModel(); if
			 * (nem.criaNegociacaoElemento(idNegocio)){ return idNegocio; }else{
			 * return -1; }
			 */
			return participante.getId();
		} catch (Exception e) {
			System.out.println(e);
			return -1;
		} finally {
			try {
				ps.close();
			} catch (Exception e) {
				System.out.println(e);
			}
			try {
				conn.close();
			} catch (Exception e) {
				System.out.println(e);
			}
		}
	}

	public void atualizaParticipanteReuniao(ParticipanteBean participante) {
		PreparedStatement ps = null;
		Connection conn = ServiceLocator.getConnection();
		try {
			String sql = "update participante set part_ds_nome=?, part_telefone1=?, part_celular=?, " +
					"part_ds_cargo=?, part_tx_observacao=? where part_cd_id=?";

			ps = conn.prepareStatement(sql);
			ps.setString(1, participante.getNome());
			ps.setString(2, participante.getTelefone());
			ps.setString(3, participante.getCelular());
			ps.setString(4, participante.getCargo());
			ps.setString(5, participante.getObservacao());
			ps.setInt(6, participante.getId());
			ps.executeUpdate();
			conn.commit();

		} catch (Exception e) {
			System.out.println(e);

		} finally {
			try {
				ps.close();
			} catch (Exception e) {
				System.out.println(e);
			}
			try {
				conn.close();
			} catch (Exception e) {
				System.out.println(e);
			}
		}
	}

	public ArrayList<ParticipanteBean> listaParticipanteporReuniao(int idReuniao) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		Connection conn = ServiceLocator.getConnection();
		try {
			String sql = "select p.part_cd_id, part_ds_nome, part_telefone1, part_ds_cargo, part_celular, part_tx_observacao "
					+ "from participante p, reuniao_participante r "
					+ "where r.part_cd_id = p.part_cd_id and reun_cd_id = "	+ idReuniao
					+ " order by r.part_cd_id";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			ArrayList<ParticipanteBean> participantes = new ArrayList<ParticipanteBean>();
			ParticipanteBean participante;
			int indice = 0;
			while (rs.next()) {
				participante = new ParticipanteBean();
				participante.setCargo(rs.getString("part_ds_cargo"));
				participante.setCelular(rs.getString("part_celular"));
				participante.setId(rs.getInt("part_cd_id"));
				participante.setNome(rs.getString("part_ds_nome"));
				participante.setObservacao(rs.getString("part_tx_observacao"));
				participante.setTelefone(rs.getString("part_telefone1"));
				participante.setIndice(indice);
				
				// participante.setReuniao(rs.getInt("NEGO_CD_TIPO_NEGOCIACAO"));
				participantes.add(participante);
				indice++;
			}
			return participantes;
		} catch (Exception e) {
			System.out.println(e);
			return null;
		} finally {
			try {
				rs.close();
			} catch (Exception e) {
				System.out.println(e);
			}
			try {
				ps.close();
			} catch (Exception e) {
				System.out.println(e);
			}
			try {
				conn.close();
			} catch (Exception e) {
				System.out.println(e);
			}
		}
	}

	public boolean removeParticipante(int idParticipante) {
		PreparedStatement ps = null;
		Connection conn = ServiceLocator.getConnection();
		try {
			String sql = "delete from reuniao_participante where part_cd_id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, idParticipante);
			ps.executeUpdate();

			sql = "delete from participante where part_cd_id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, idParticipante);
			ps.executeUpdate();
			conn.commit();

			return true;
		} catch (Exception e) {
			System.out.println(e);
			return false;
		} finally {
			try {
				ps.close();
			} catch (Exception e) {
				System.out.println(e);
			}
			try {
				conn.close();
			} catch (Exception e) {
				System.out.println(e);
			}
		}
	}

	public boolean removeParticipantePorReuniao(int idReuniao) {
		PreparedStatement ps = null;
		PreparedStatement ps2 = null;
		ResultSet rs = null;
		Connection conn = ServiceLocator.getConnection();
		try {

			String sql = "select part_cd_id from reuniao_participante where reun_cd_id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, idReuniao);
			rs = ps.executeQuery();

			while (rs.next()) {
				int idParticipante = rs.getInt("part_cd_id");
				sql = "delete from reuniao_participante where reun_cd_id = ? and part_cd_id = ?";
				ps = conn.prepareStatement(sql);
				ps.setInt(1, idReuniao);
				ps.setInt(2, idParticipante);
				ps.executeUpdate();
				
				sql = "delete from participante where part_cd_id = ?";
				ps2 = conn.prepareStatement(sql);
				ps2.setInt(1, idParticipante);
				ps2.executeUpdate();
			
			}

			conn.commit();
			return true;
		} catch (Exception e) {
			System.out.println(e);
			return false;
		} finally {
			try {
				ps.close();
			} catch (Exception e) {
				System.out.println(e);
			}
			try {
				conn.close();
			} catch (Exception e) {
				System.out.println(e);
			}
		}
	}

	public boolean removeParticipantesExcluidosPorReuniao(int idReuniao, ArrayList<Integer> participantesAtivos) {
		PreparedStatement ps = null;
		PreparedStatement ps2 = null;
		ResultSet rs = null;
		Connection conn = ServiceLocator.getConnection();
		try {

			String sql = "select part_cd_id from reuniao_participante where reun_cd_id = ?";
			for(int i = 0; i < participantesAtivos.size(); i++)
				sql += " and part_cd_id <> ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, idReuniao);
			for(int i = 0; i < participantesAtivos.size(); i++)
				ps.setInt(i+2, participantesAtivos.get(i));
			rs = ps.executeQuery();

			while (rs.next()) {
				int idParticipante = rs.getInt("part_cd_id");
				sql = "delete from reuniao_participante where reun_cd_id = ? and part_cd_id = ?";
				ps = conn.prepareStatement(sql);
				ps.setInt(1, idReuniao);
				ps.setInt(2, idParticipante);
				ps.executeUpdate();
				
				sql = "delete from participante where part_cd_id = ?";
				ps2 = conn.prepareStatement(sql);
				ps2.setInt(1, idParticipante);
				ps2.executeUpdate();
			
			}

			conn.commit();
			return true;
		} catch (Exception e) {
			System.out.println(e);
			return false;
		} finally {
			try {
				ps.close();
			} catch (Exception e) {
				System.out.println(e);
			}
			try {
				conn.close();
			} catch (Exception e) {
				System.out.println(e);
			}
		}
	}
	
	public ParticipanteBean getParticipante(int idParticipante) {
		ParticipanteBean pb = new ParticipanteBean();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Connection conn = ServiceLocator.getConnection();

		try {
			String sql = "select * from participante where part_cd_id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, idParticipante);
			rs = ps.executeQuery();
			rs.next();
			pb.setCargo(rs.getString("part_ds_cargo"));
			pb.setCelular(rs.getString("part_celular"));
			pb.setId(rs.getInt("part_cd_id"));
			pb.setNome(rs.getString("part_ds_nome"));
			pb.setObservacao(rs.getString("part_tx_observacao"));
			pb.setTelefone(rs.getString("part_telefone1"));
			return pb;
		} catch (Exception e) {
			System.out.println(e);
			return null;
		} finally {
			try {
				rs.close();
			} catch (Exception e) {
				System.out.println(e);
			}
			try {
				ps.close();
			} catch (Exception e) {
				System.out.println(e);
			}
			try {
				conn.close();
			} catch (Exception e) {
				System.out.println(e);
			}
		}
	}
}
